package com.centit.importData.po;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.centit.airplanebaseinfo.po.AirplaneBaseinfo;
import com.centit.airplanebaseinfo.service.AirplaneBaseinfoManager;
import com.centit.importData.dao.JdndjhDao;
import com.centit.importData.dao.ShhzfjfdjDao;
import com.centit.support.algorithm.UuidOpt;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExportExcelUtil {
    private ShhzfjfdjDao shhzfjfdjDao;
    private AirplaneBaseinfoManager airplaneBaseinfoManager;
    private JdndjhDao jdndjhDao;
    public void initDao(ShhzfjfdjDao shhzfjfdjDao,
                        AirplaneBaseinfoManager airplaneBaseinfoManager,
                        JdndjhDao jdndjhDao){
        this.shhzfjfdjDao=shhzfjfdjDao;
        this.airplaneBaseinfoManager=airplaneBaseinfoManager;
        this.jdndjhDao=jdndjhDao;
    }

    //导入表：售后回租飞机发动机  导入日志
    public static Map<String,List<String>> RESULT_STR_LIST_All_POOL_NP=new HashMap<>();
    public static int SHHZFJFDJ_SHEET_CELL_NUM=27;//售后回租飞机发动机表列数
    public static Integer[] SHHZFJFDJ_SHEET_DATE_CELL={17};

    //导入表：机队年度计划
    public static Map<String,List<String>> RESULT_STR_LIST_All_POOL_JDNDJH=new HashMap<>();
    public static int JDNDJH_SHEET_CELL_NUM=10;//机队年度计划表列数
    public static Integer[] JDNDJH_SHEET_DATE_CELL={4,5,9};

    //导入表：机队基本信息
    public static Map<String,List<String>> RESULT_STR_LIST_All_POOL_JDJBXX=new HashMap<>();
    public static int JDJBXX_SHEET_CELL_NUM=42;//机队基本信息列数
    public static Integer[] JDJBXX_SHEET_DATE_CELL={3,4,10,11,18,23,28,29,30,34,36};


    public static boolean streamSearch(Integer[] array, int target) {
        return Arrays.stream(array).anyMatch(element -> element == target);
    }
    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell) throws Exception{
        if(cell == null) return "";
        if(cell.getCellType() == 1){
            return cell.getStringCellValue();
        }else if(cell.getCellType() == 4){
            return String.valueOf(cell.getBooleanCellValue());
        }else if(cell.getCellType() == 2){
            return cell.getCellFormula() ;
        }else if(cell.getCellType() == 0){
            //数值类型去。0
            String numValue= String.valueOf(cell.getNumericCellValue());
            if(numValue.endsWith(".0")){
                numValue=numValue.substring(0,numValue.length()-2);
            }
            return numValue;
        }
        return cell.getStringCellValue();

    }

    /**
     * 导入：售后回租飞机发动机
     * @param file
     * @param loginUser
     * @param type
     * @return
     * @throws Exception
     */
    public String importExcelShhzfj(MultipartFile file
            , String loginUser, String type) throws Exception{
        Sheet sheet;
        SimpleDateFormat shortDate=new SimpleDateFormat("yyyy-MM-dd");
        if(type.endsWith(".xls")){
            Workbook workbook= new HSSFWorkbook(file.getInputStream());
            sheet = workbook.getSheetAt(0);    //获得第一个表单sheet
        }else {
            XSSFWorkbook workbook= new XSSFWorkbook(file.getInputStream());
            sheet = workbook.getSheetAt(0);    //获得第一个表单sheet
        }
        int count = sheet.getLastRowNum()+1;//总行数
        //System.out.println("-----count------"+count);
        /*step1:解析数据*/
        RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("-----------step1:解析数据------------");
        List<Map<Integer,String[]>> sheetDataList=new ArrayList<>();
        try {
            for(int i = 1; i < count;i++){  //第一行是标题
                Row row = sheet.getRow(i);
                if(row!=null){
                    Map<Integer,String[]> rowData=new HashMap<>();
                    String[] thisRowDataArr=new String[SHHZFJFDJ_SHEET_CELL_NUM];
                    for(int j=0;j<SHHZFJFDJ_SHEET_CELL_NUM;j++) {
                        String lineContent = "";
                        try {
                            lineContent = getCellValue(row.getCell(j));
                            //某些列需要转日期
                            if(streamSearch(SHHZFJFDJ_SHEET_DATE_CELL,j)){
                                Date date = HSSFDateUtil.getJavaDate(Double.parseDouble(lineContent));
                                lineContent=shortDate.format(date);
                            }
                        } catch (Exception e) {
                            RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("error:第"+(i+1)+"行，第"+(j+1)+"列数据解析失败");
                        }
                        thisRowDataArr[j]=lineContent;
                    }
                    rowData.put(i,thisRowDataArr);
                    sheetDataList.add(rowData);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("wrong:步骤一执行中遇到不可忽视的错误，已终止程序");
            return "";
        }
        RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("-----------步骤一:解析数据结束------------");

        /*step2:解析数据*/
        RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("-----------step2:处理数据并入库------------");
        List<Shhzfjfdj> shhzfjfdjs=new ArrayList<>();
        try {
            sheetDataList.stream().forEach(r->{
                Map<Integer,String[]> rowData=r;
                //基本上每个map只有一条数据
                rowData.forEach((key,value)->{
                    String[] rowDataArr=value;
                    Shhzfjfdj shhzfjfdj=new Shhzfjfdj(UuidOpt.getUuidAsString32(),String.valueOf(key),
                            rowDataArr[0],rowDataArr[1],rowDataArr[2],rowDataArr[3],
                            rowDataArr[4],rowDataArr[5],rowDataArr[6],rowDataArr[7],
                            rowDataArr[8],rowDataArr[9],rowDataArr[10],rowDataArr[11],
                            rowDataArr[12],rowDataArr[13],rowDataArr[14],rowDataArr[15],
                            rowDataArr[16],rowDataArr[17],rowDataArr[18],rowDataArr[19],
                            rowDataArr[20],rowDataArr[21],rowDataArr[22],rowDataArr[23],
                            rowDataArr[24],rowDataArr[25],rowDataArr[26]);
                    shhzfjfdjDao.saveNewObject(shhzfjfdj);
                    shhzfjfdjs.add(shhzfjfdj);
                });
            });

        }catch (Exception e){
            e.printStackTrace();
            RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("wrong:步骤二执行中遇到不可忽视的错误，已终止程序");
            return "";
        }
        RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("-----------步骤二:处理数据并入库结束------------");
        /*step3:关联机队基本信息*/
        RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("-----------step3:关联机队基本信息------------");
        try {
            Map<String,AirplaneBaseinfo> airplaneBaseinfoMap=new HashMap<>();
            shhzfjfdjs.stream().forEach(s->{
                //有key说明是发动机2
                if(airplaneBaseinfoMap.containsKey(s.getNoNameLineW())){
                    airplaneBaseinfoMap.get(s.getNoNameLineW()).setTzsfdjxlh2(s.getEsn());
                    airplaneBaseinfoMap.get(s.getNoNameLineW()).setFdjzt2(s.getMark1());
                }else {
                    //没有说明是发动机1
                    AirplaneBaseinfo airplaneBaseinfo=airplaneBaseinfoManager.
                            getObjectByProperty("sign",s.getNoNameLineW());
                    if(airplaneBaseinfo!=null){
                        airplaneBaseinfo.setTzsfdjxlh1(s.getEsn());
                        airplaneBaseinfo.setFdjzt1(s.getMark1());
                        airplaneBaseinfoMap.put(s.getNoNameLineW(),airplaneBaseinfo);
                    }else {
                        RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("error:退出机号："+s.getNoNameLineW()+",该条数据未关联到机队信息");
                    }
                }
            });
            //数据处理好之后修改入库
            airplaneBaseinfoMap.forEach((key,value)->{
                try {
                    airplaneBaseinfoManager.mergeObject(value);
                }catch (Exception e){
                    e.printStackTrace();
                    RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("error:机队主键："+value.getRecId()+",该条数据更新保存失败");
                }

            });

        }catch (Exception e){
            e.printStackTrace();
            RESULT_STR_LIST_All_POOL_NP.get(loginUser).add("wrong:步骤三执行中遇到不可忽视的错误，已终止程序");
            return "";
        }


        return "";
    }



    /**
     * 导入：机队年度计划
     * @param file
     * @param loginUser
     * @param type
     * @return
     * @throws Exception
     */
    public String importExcelJdndjh(MultipartFile file
            , String loginUser, String type) throws Exception{
        Sheet sheet;
        SimpleDateFormat shortDate=new SimpleDateFormat("yyyy-MM-dd");
        if(type.endsWith(".xls")){
            Workbook workbook= new HSSFWorkbook(file.getInputStream());
            sheet = workbook.getSheetAt(0);    //获得第一个表单sheet
        }else {
            XSSFWorkbook workbook= new XSSFWorkbook(file.getInputStream());
            sheet = workbook.getSheetAt(0);    //获得第一个表单sheet
        }
        int count = sheet.getLastRowNum()+1;//总行数
        //System.out.println("-----count------"+count);
        /*step1:解析数据*/
        RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("-----------step1:解析数据------------");
        List<Map<Integer,String[]>> sheetDataList=new ArrayList<>();
        try {
            for(int i = 1; i < count;i++){  //第一行是标题
                Row row = sheet.getRow(i);
                if(row!=null){
                    Map<Integer,String[]> rowData=new HashMap<>();
                    String[] thisRowDataArr=new String[JDNDJH_SHEET_CELL_NUM];
                    for(int j=0;j<JDNDJH_SHEET_CELL_NUM;j++) {
                        String lineContent = "";
                        try {
                            lineContent = getCellValue(row.getCell(j));
                            //某些列需要转日期
                            if(streamSearch(JDNDJH_SHEET_DATE_CELL,j)){
                                Date date = HSSFDateUtil.getJavaDate(Double.parseDouble(lineContent));
                                lineContent=shortDate.format(date);
                            }
                        } catch (Exception e) {
                            RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("error:第"+(i+1)+"行，第"+(j+1)+"列数据解析失败");
                        }
                        thisRowDataArr[j]=lineContent;
                    }
                    rowData.put(i,thisRowDataArr);
                    sheetDataList.add(rowData);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("wrong:步骤一执行中遇到不可忽视的错误，已终止程序");
            return "";
        }
        RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("-----------步骤一:解析数据结束------------");

        /*step2:解析数据*/
        RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("-----------step2:处理数据并入库------------");
        List<Jdndjh> jdndjhs=new ArrayList<>();
        try {
            sheetDataList.stream().forEach(r->{
                Map<Integer,String[]> rowData=r;
                //基本上每个map只有一条数据
                rowData.forEach((key,value)->{
                    String[] rowDataArr=value;
                    Jdndjh jdndjh=new Jdndjh(UuidOpt.getUuidAsString32(),String.valueOf(key),
                            rowDataArr[0],rowDataArr[1],rowDataArr[2],rowDataArr[3],
                            rowDataArr[4],rowDataArr[5],rowDataArr[6],rowDataArr[7],
                            rowDataArr[8],rowDataArr[9]);
                    System.out.println("-----jdndjh----"+JSON.toJSONString(jdndjh));
                    jdndjhDao.saveNewObject(jdndjh);
                    jdndjhs.add(jdndjh);
                });
            });

        }catch (Exception e){
            e.printStackTrace();
            RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("wrong:步骤二执行中遇到不可忽视的错误，已终止程序");
            return "";
        }
        RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("-----------步骤二:处理数据并入库结束------------");
        /*step3:关联机队基本信息*/
        RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("-----------step3:关联机队基本信息------------");
        try {
            Map<String,AirplaneBaseinfo> airplaneBaseinfoMap=new HashMap<>();
            jdndjhs.stream().forEach(s->{
                AirplaneBaseinfo airplaneBaseinfo=airplaneBaseinfoManager.
                        getObjectByProperty("sign",s.getJh());
                if(airplaneBaseinfo!=null){
                    airplaneBaseinfo.setZjycdxrwmc(s.getRwmc());
                    if(StringUtils.isEmpty(s.getKssj())){
                        RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("error:机号："+s.getJh()+",该条数据开始时间为空，请检查时未填写还是转换失败");
                    }else {
                        try {
                            airplaneBaseinfo.setJhdxxyckssjT(shortDate.parse(s.getKssj()));
                            airplaneBaseinfo.setJhxrwmc(s.getRwmc());
                        } catch (ParseException e) {
                            e.printStackTrace();
                            RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("error:机号："+s.getJh()+",该条数据开始时间转换报错");
                        }
                    }

                    airplaneBaseinfoMap.put(s.getJh(),airplaneBaseinfo);
                }else {
                    RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("error:退出机号："+s.getJh()+",该条数据未关联到机队信息");
                }
            });
            //数据处理好之后修改入库
            airplaneBaseinfoMap.forEach((key,value)->{
                try {
                    airplaneBaseinfoManager.mergeObject(value);
                }catch (Exception e){
                    e.printStackTrace();
                    RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("error:机队主键："+value.getRecId()+",该条数据更新保存失败");
                }

            });

        }catch (Exception e){
            e.printStackTrace();
            RESULT_STR_LIST_All_POOL_JDNDJH.get(loginUser).add("wrong:步骤三执行中遇到不可忽视的错误，已终止程序");
            return "";
        }


        return "";
    }


    /*---------------------------------------------------------------------------*/
    /*导出excel*/
    private static String[] szbColumn={"国籍登记证编号","国籍登记证签发日期","国籍证发给",
            "所有人名称","所有人地址","标准适航证编号","标准适航证签发日期","选择呼叫代码",
            "航空器地址编码（S模式）HEX","航空器地址编码（S模式）OCT","电台执照序号",
            "电台执照颁发日期","电台执照有效期限至","电台执照监控报警日期"};
    private static String[] szbColumnKey={"gjNo","gjDate","bzfj","haveName","haveAddr",
            "shNo","qfDate","hjNo","addrNoHex","addrNoOct","dtNo","dtbfDate","dtyxDate",
            "dtzzjkbjrq"};
    private static String[] zlwxbColumn={"航空器型号","MSN号","出厂日期","进苏日期","发动机型号",
            "机龄","性质","出租人","起租日","退租日","租期","是否可提前退租","是否到期无异议自动续租",
            "租金","已有飞行小时N2","剩余飞行小时","DSG到期日","机身最近大修开始时间","最近一次大修任务名称",
            "机身预期下一次大修开始时间","机身预期再下一次大修开始时间","机身计划大修下一次开始时间",
            "计划修任务名称","退租时发动机序列号1","发动机1状态","退租时发动机序列号2","发动机2状态",
            "退租日期前半年内计划大修","退租日期前半年内预期大修"};
    private static String[] zlwxbColumnKey={"airModel","ccxlh","proctionDate","useDate","engine","jl",
            "nature","czr","startTime","endTime","zq","isTqtz","isWyyzdxz","mqzj","yyfxxs","syfxxs",
            "dsgDqr","dxkssjQ","zjycdxrwmc","dxksslQ1","dxkssjQ2","jhdxxyckssjT","jhxrwmc","tzsfdjxlh1",
            "fdjzt1","tzsfdjxlh2","fdjzt2","tzrqqbnnjhdx","tzrqqbnnyqdx"};
    private static String[] zbColumn={"国籍登记证编号","国籍登记证签发日期","国籍证发给",
            "所有人名称","所有人地址","标准适航证编号","标准适航证签发日期","选择呼叫代码",
            "航空器地址编码（S模式）HEX","航空器地址编码（S模式）OCT","电台执照序号",
            "电台执照颁发日期","电台执照有效期限至","电台执照监控报警日期","航空器型号","MSN号","出厂日期","进苏日期","发动机型号",
            "机龄","性质","出租人","起租日","退租日","租期","是否可提前退租","是否到期无异议自动续租",
            "租金","已有飞行小时N2","剩余飞行小时","DSG到期日","机身最近大修开始时间","最近一次大修任务名称",
            "机身预期下一次大修开始时间","机身预期再下一次大修开始时间","机身计划大修下一次开始时间",
            "计划修任务名称","退租时发动机序列号1","发动机1状态","退租时发动机序列号2","发动机2状态",
            "退租日期前半年内计划大修","退租日期前半年内预期大修"};
    private static String[] zbColumnKey={"gjNo","gjDate","bzfj","haveName","haveAddr",
            "shNo","qfDate","hjNo","addrNoHex","addrNoOct","dtNo","dtbfDate","dtyxDate",
            "dtzzjkbjrq","airModel","ccxlh","proctionDate","useDate","engine","jl",
            "nature","czr","startTime","endTime","zq","isTqtz","isWyyzdxz","mqzj","yyfxxs","syfxxs",
            "dsgDqr","dxkssjQ","zjycdxrwmc","dxksslQ1","dxkssjQ2","jhdxxyckssjT","jhxrwmc","tzsfdjxlh1",
            "fdjzt1","tzsfdjxlh2","fdjzt2","tzrqqbnnjhdx","tzrqqbnnyqdx"};
    public void exportSzb(JSONArray jsonArray, String title, HttpServletRequest request, HttpServletResponse response) throws Exception {
        InputStream is = null;
        OutputStream ouputStream = null;
        try {
            //读取模板
            String filepath = request.getSession().getServletContext().getRealPath("/")
                    + "Document" + File.separator + "空白文档.xls";
            is = new FileInputStream(filepath);
            HSSFWorkbook workbook = new HSSFWorkbook(is);
            HSSFSheet sheet = workbook.getSheetAt(0);
            /*第一行是标题*/
            HSSFRow firstRow = sheet.createRow(0);
            String[] columnName=szbColumn;
            String[] columnKey=szbColumnKey;
            switch (title){
                case "三证表":
                    columnName=szbColumn;
                    columnKey=szbColumnKey;
                    break;
                case "租赁维修表":
                    columnName=zlwxbColumn;
                    columnKey=zlwxbColumnKey;
                    break;
                    default:
                        //总表
                        columnName=zbColumn;
                        columnKey=zbColumnKey;
                        break;

            }
            /*firstRow.createCell(0).setCellValue("国籍登记证编号");
            firstRow.createCell(1).setCellValue("国籍登记证签发日期");
            firstRow.createCell(2).setCellValue("国籍证发给");
            firstRow.createCell(3).setCellValue("所有人名称");
            firstRow.createCell(4).setCellValue("所有人地址");
            firstRow.createCell(5).setCellValue("标准适航证编号");
            firstRow.createCell(6).setCellValue("标准适航证签发日期");
            firstRow.createCell(7).setCellValue("选择呼叫代码");
            firstRow.createCell(8).setCellValue("航空器地址编码（S模式）HEX");
            firstRow.createCell(9).setCellValue("航空器地址编码（S模式）OCT");
            firstRow.createCell(10).setCellValue("电台执照序号");
            firstRow.createCell(11).setCellValue("电台执照颁发日期");
            firstRow.createCell(12).setCellValue("电台执照有效期限至");
            firstRow.createCell(13).setCellValue("电台执照监控报警日期");*/
            for(int i=0;i<columnName.length;i++){
                firstRow.createCell(i).setCellValue(columnName[i]);
            }
            //获取数据
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            if (CollectionUtils.isNotEmpty(jsonArray)) {
                for (Object e : jsonArray) {
                    JSONObject jsonObject=(JSONObject)e;
                    int lastRowNum = sheet.getLastRowNum();
                    HSSFRow lastRow = sheet.createRow(lastRowNum + 1);

                    for(int i=0;i<columnName.length;i++){
                        lastRow.createCell(i).setCellValue(jsonObject.getString(columnKey[i]));
                    }
                    /*lastRow.createCell(0).setCellValue(jsonObject.getString("gjNo"));
                    lastRow.createCell(1).setCellValue(jsonObject.getString("gjDate"));
                    lastRow.createCell(2).setCellValue(jsonObject.getString("bzfj"));
                    lastRow.createCell(3).setCellValue(jsonObject.getString("haveName"));
                    lastRow.createCell(4).setCellValue(jsonObject.getString("haveAddr"));
                    lastRow.createCell(5).setCellValue(jsonObject.getString("shNo"));
                    lastRow.createCell(6).setCellValue(jsonObject.getString("qfDate"));
                    lastRow.createCell(7).setCellValue(jsonObject.getString("hjNo"));
                    lastRow.createCell(8).setCellValue(jsonObject.getString("addrNoHex"));
                    lastRow.createCell(9).setCellValue(jsonObject.getString("addrNoOct"));
                    lastRow.createCell(10).setCellValue(jsonObject.getString("dtNo"));
                    lastRow.createCell(11).setCellValue(jsonObject.getString("dtbfDate"));
                    lastRow.createCell(12).setCellValue(jsonObject.getString("dtyxDate"));
                    lastRow.createCell(13).setCellValue(jsonObject.getString("dtzzjkbjrq"));*/
                }
            }
            // 先去掉文件名称中的空格,然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名
            String fileName = title+".xls";
            response.addHeader("Content-Disposition", "attachment;filename="
                    + new String(
                    fileName.replaceAll(" ", "").getBytes("utf-8"),
                    "iso8859-1"));
            response.setContentType("multipart/form-data");
            ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
        } catch (FileNotFoundException e) {

        } catch (IOException e) {

        } finally {
            if (is != null) {
                is.close();
            }
            if (ouputStream != null) {
                ouputStream.close();
            }
        }
    }


    /*---------------------------------------------------------*/
    /**
     * 导入：机队基本信息导入
     * @param file
     * @param loginUser
     * @param type
     * @return
     * @throws Exception
     */
    public String importExcelJdjbxx(MultipartFile file
            , String loginUser, String type) throws Exception{
        Sheet sheet;
        SimpleDateFormat shortDate=new SimpleDateFormat("yyyy-MM-dd");
        if(type.endsWith(".xls")){
            Workbook workbook= new HSSFWorkbook(file.getInputStream());
            sheet = workbook.getSheetAt(0);    //获得第一个表单sheet
        }else {
            XSSFWorkbook workbook= new XSSFWorkbook(file.getInputStream());
            sheet = workbook.getSheetAt(0);    //获得第一个表单sheet
        }
        int count = sheet.getLastRowNum()+1;//总行数
        /*step1:解析数据*/
        List<Map<Integer,String[]>> sheetDataList=new ArrayList<>();
        try {
            for(int i = 1; i < count;i++){  //第一行是标题
                Row row = sheet.getRow(i);
                if(row!=null){
                    Map<Integer,String[]> rowData=new HashMap<>();
                    String[] thisRowDataArr=new String[JDJBXX_SHEET_CELL_NUM];
                    for(int j=0;j<JDJBXX_SHEET_CELL_NUM;j++) {
                        String lineContent = "";
                        try {
                            lineContent = getCellValue(row.getCell(j));
                            //某些列需要转日期
                            if(streamSearch(JDJBXX_SHEET_DATE_CELL,j)){
                                Date date = HSSFDateUtil.getJavaDate(Double.parseDouble(lineContent));
                                lineContent=shortDate.format(date);
                            }
                        } catch (Exception e) {
                            if(streamSearch(JDJBXX_SHEET_DATE_CELL,j)){
                                lineContent="1900-01-01";
                            }
                            RESULT_STR_LIST_All_POOL_JDJBXX.get(loginUser).add("error:第"+(i+1)+"行，第"+(j+1)+"列数据解析失败");
                        }
                        thisRowDataArr[j]=lineContent;
                    }
                    rowData.put(i,thisRowDataArr);
                    sheetDataList.add(rowData);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            RESULT_STR_LIST_All_POOL_JDJBXX.get(loginUser).add("wrong:步骤一执行中遇到不可忽视的错误，已终止程序");
            return "";
        }
        /*step2:解析数据*/
        RESULT_STR_LIST_All_POOL_JDJBXX.get(loginUser).add("-----------step2:处理数据并入库------------");

        try {
            sheetDataList.stream().forEach(r->{
                Map<Integer,String[]> rowData=r;
                //基本上每个map只有一条数据
                rowData.forEach((key,value)->{
                    String[] rowDataArr=value;
                    try {
                        AirplaneBaseinfo airplaneBaseinfo=new AirplaneBaseinfo(
                                UuidOpt.getUuidAsString32(),rowDataArr[0], rowDataArr[1], rowDataArr[2],
                                shortDate.parse(rowDataArr[3]), shortDate.parse(rowDataArr[4]), rowDataArr[5], rowDataArr[6],
                                rowDataArr[7], rowDataArr[8], rowDataArr[9], shortDate.parse(rowDataArr[10]),
                                shortDate.parse(rowDataArr[11]), rowDataArr[12], rowDataArr[13], rowDataArr[14],
                                rowDataArr[15], rowDataArr[16], rowDataArr[17], shortDate.parse(rowDataArr[18]),
                                rowDataArr[19], rowDataArr[20], rowDataArr[21], rowDataArr[22],
                                shortDate.parse(rowDataArr[23]), rowDataArr[24], rowDataArr[25], rowDataArr[26],
                                rowDataArr[27], shortDate.parse(rowDataArr[28]),shortDate.parse(rowDataArr[29]), shortDate.parse(rowDataArr[30]),
                                rowDataArr[31], rowDataArr[32], rowDataArr[33], shortDate.parse(rowDataArr[34]),
                                rowDataArr[35], shortDate.parse(rowDataArr[36]), rowDataArr[37], rowDataArr[38],
                                rowDataArr[39], rowDataArr[40], rowDataArr[41]);
                        System.out.println("----airplaneBaseinfo----"+JSON.toJSONString(airplaneBaseinfo));
                        airplaneBaseinfo.setDataValid("T");
                        airplaneBaseinfo.setStatus("2");
                        //计算值
                        airplaneBaseinfo=airplaneBaseinfo.calculatedValue(airplaneBaseinfo);
                        airplaneBaseinfoManager.saveNewObject(airplaneBaseinfo);

                    }catch (Exception e){
                        e.printStackTrace();
                    }

                });
            });

        }catch (Exception e){
            e.printStackTrace();
            RESULT_STR_LIST_All_POOL_JDJBXX.get(loginUser).add("wrong:步骤二执行中遇到不可忽视的错误，已终止程序");
            return "";
        }
        RESULT_STR_LIST_All_POOL_JDJBXX.get(loginUser).add("-----------步骤二:处理数据并入库结束------------");

        return "";
    }



}
